package com.pms.utils;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * Created by Administrator on 2016/8/27.
 */
public class ExcelUtil {


//    public static void main(String[] args){
//        String path = "d://tt.xls";
//        File excelFile = new File(path); // 创建文件对象
//        try {
//            FileInputStream is = new FileInputStream(excelFile); // 文件流
//
//            readExcelFile(is, BlackListDetailImport.class);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }
	
	public static List<String> getTableHead(byte[] excelData,int headRowNo) throws Exception{
		InputStream is = new ByteArrayInputStream(excelData);
		Workbook workbook = null;
        try{
            workbook = WorkbookFactory.create(is);
            int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
            if(sheetCount == 0)
            	return null;
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getPhysicalNumberOfRows();
            if(rowCount < headRowNo){
            	return null;
            }
            Row row1 = sheet.getRow(headRowNo-1);
            int cellCount = row1.getPhysicalNumberOfCells();
            ArrayList<String> headList = new ArrayList<String>(cellCount);
            for(int i = 0 ; i < cellCount; i++){
                Cell cell = row1.getCell(i);
                String cellValue = cell.getStringCellValue();
                headList.add(cellValue);
            }
            return headList;
        }catch(Exception e){
            throw new Exception("文件读取出错，请确保为Excel文件!");
        }
	}
	public static <T> List<T> readExcel(byte[] excelData,Class<T> beanClass,Map<String,String> fieldsMap,int headRowNo){
		List<T> records = new ArrayList<T>();
		InputStream is = new ByteArrayInputStream(excelData);
		Workbook workbook = null;
        try {
		    workbook = WorkbookFactory.create(is);
			
            int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
            if(sheetCount == 0)
            	return null;
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getPhysicalNumberOfRows();
            if(rowCount < headRowNo){
            	return null;
            }
            Row row1 = sheet.getRow(headRowNo-1);
            
            int cellCount = row1.getPhysicalNumberOfCells();
            String[] head = new String[cellCount];
            for(int i = 0 ; i < cellCount; i++){
                Cell cell = row1.getCell(i);
                String cellValue = cell.getStringCellValue();
                head[i] = cellValue;
            }
            for (int r = headRowNo; r < rowCount; r++) {
                Row row = sheet.getRow(r);
                T bean = beanClass.newInstance();
                for(int j = 0 ; j < cellCount; j++){
                	Cell cell = row.getCell(j);
                	if(null == cell)
                		continue;
                	String headName = head[j];
                	String fieldName = fieldsMap.get(headName);
                	if(null != fieldName && !"0".equals(fieldName)){
                		Field field = beanClass.getDeclaredField(fieldName);
                        field.setAccessible(true);
                        if (field.getType() == String.class) {
                            if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                                field.set(bean, cell.getNumericCellValue()+"");
                            else
                                field.set(bean, cell.getStringCellValue());
                        } else if (field.getType() == int.class || field.getType() == Integer.class) {
                            field.set(bean, (int) cell.getNumericCellValue());
                        } else if (field.getType() == double.class || field.getType() == Double.class) {
                            field.set(bean, cell.getNumericCellValue());
                        } else if (field.getType() == Date.class) {
                            field.set(bean, cell.getDateCellValue());
                        } else {
                            field.set(bean, cell.getStringCellValue());
                        }
                	}
                }
                records.add(bean);
            }
        } catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        return records;
	}
	
    public static <T> List<T> readExcel(byte[] excelData, Class<T> beanClass) throws Exception {
        try {
           InputStream is = new ByteArrayInputStream(excelData);
            return readExcelFile(is, beanClass);
        } catch (Exception e) {
            throw new Exception("分析Excel文件失败");
        }
    }
    public static  <T> List<T> readExcelFile(InputStream is,  Class<T> beanClass) throws Exception {

        List<T> records = new ArrayList<T>();
        Field[] fields = beanClass.getDeclaredFields();
        // ******************header 的排序顺序与javabean里的定义要一致*****************************
        String[] header = {"姓名","身份证号码","电话","电子邮箱","贷款地点","贷款日期","贷款类型","贷款形式",
                "逾期开始日期","逾期金额","逾期状态","逾期次数","逾期最长时长","户籍地址","居住地址","原因"};
        Workbook workbook = null;
        try{
            workbook = WorkbookFactory.create(is);
        }catch(Exception e){
            throw new Exception("文件读取出错，请确保为Excel文件!");
        }
        int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
        Integer[] mapCell = new Integer[header.length];
        // 遍历每个Sheet
        for (int s = 0; s < sheetCount; s++) {
            Sheet sheet = workbook.getSheetAt(s);
            int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
            // 遍历每一行
            // 获取第一行，以及第一行的列数
            Row row1 = sheet.getRow(0);
            int cellCount = row1.getPhysicalNumberOfCells();
            if( cellCount < header.length)
                throw new Exception("必填信息不全");
            String cellValue = null;
            for(int i = 0 ; i < cellCount; i++){
                Cell cell = row1.getCell(i);
                cellValue = cell.getStringCellValue();
                for(int j = 0; j < header.length; j++) {
                    if (cellValue.equals(header[j])) {
                        mapCell[j] = i;
                        break;
                    }
                }
            }
            for (int r = 1; r < rowCount; r++) {
                Row row = sheet.getRow(r);
                T bean = beanClass.newInstance();
                for (int i = 0; i < mapCell.length; i++) {
                    int c = mapCell[i];
                    Cell cell = row.getCell(c);
                    if (cell != null) {
                        fields[i].setAccessible(true);

                        if (fields[i].getType() == String.class) {
                            if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                                fields[i].set(bean, cell.getNumericCellValue()+"");
                            else
                                fields[i].set(bean, cell.getStringCellValue());
                        } else if (fields[i].getType() == int.class || fields[i].getType() == Integer.class) {
                            fields[i].set(bean, (int) cell.getNumericCellValue());
                        } else if (fields[i].getType() == double.class || fields[i].getType() == Double.class) {
                            fields[i].set(bean, cell.getNumericCellValue());
                        } else if (fields[i].getType() == Date.class) {
                            fields[i].set(bean, cell.getDateCellValue());
                        } else {
                            fields[i].set(bean, cell.getStringCellValue());
                        }
                    }
//
//
//                        int cellType = cell.getCellType();
//                        switch (cellType) {
//                            case Cell.CELL_TYPE_STRING: // 文本
//                                cellValue = cell.getStringCellValue();
//                                break;
//                            case Cell.CELL_TYPE_NUMERIC: // 数字、日期
//                                if (DateUtil.isCellDateFormatted(cell)) {
//                                    cellValue = fmt.format(cell.getDateCellValue()); // 日期型
//                                } else {
//                                    cellValue = String.valueOf((int) cell.getNumericCellValue()); // 数字
//                                    sortsno = (int) cell.getNumericCellValue();
//                                }
//                                break;
//                            case Cell.CELL_TYPE_BOOLEAN: // 布尔型
//                                cellValue = String.valueOf(cell.getBooleanCellValue());
//                                break;
//                            case Cell.CELL_TYPE_BLANK: // 空白
//                                cellValue = cell.getStringCellValue();
//                                break;
//                            case Cell.CELL_TYPE_ERROR: // 错误
//                                cellValue = null;
//                                break;
//                            case Cell.CELL_TYPE_FORMULA: // 公式
//                                cellValue = null;
//                                break;
//                            default:
//                                cellValue = null;
//                        }
//                    }
//                    if(cellValue == null){
//                        throw new Exception("缺少必须字段");
//                    }
//                    fields[i].set(bean,cellValue);
                }
                records.add(bean);
            }
        }
        is.close();
        return records;
    }
}
